﻿using CallCenter.Data.Base;
using CallCenter.Data.Models;
using CallCenter.Data.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Hosting;

namespace CallCenter.Data.BizUtilities
{
  public  class ProductsUtility:BaseUtility
    {
        public ProductsUtility(DataManager manager)
            : base(manager) { }

  
        public ReturnMessage<ProductGridViewModel> InsertToProduct(ProductGridViewModel model)
        {
            var returnMessage = new ReturnMessage<ProductGridViewModel>();
          if(model.product.Length == 0)
            {
                returnMessage.Message = "Product Must have a name";
            }
          
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            //  int companyId = 0;

            var id = this.Insert(model.product, model.statusId, model.description,model.AuthorEmployeeId, model.notes, DateTime.Now, model.vendorcompanyId);


            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;

                returnMessage.Message = "Recordhas been  inserted to database";
            }

            return returnMessage;
        }
        public ReturnMessage<ProductGridViewModel> UpdateToProduct(ProductGridViewModel model)
        {
            var returnMessage = new ReturnMessage<ProductGridViewModel>();
            if (model.product.Length == 0)
            {
                returnMessage.Message = "Product Must have a name";
            }


            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            var id = this.Update(model.id,model.product, model.statusId, model.description, model.AuthorEmployeeId, model.notes,model.vendorcompanyId, DateTime.Now);


            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
                returnMessage.Message = "Record  has been Updated to database";
            }

            return returnMessage;
        }

        public List<ProductGridViewModel> GetAll() //Gets all the data form the contacts table
        {
            //string sql ="SELECT p.id, p.product,p.status as status,p.description as statusName,p.originalAuthorEmployeeId,p.notes from ddlProducts p where p.product like 'None' UNION ALL " +
            //     " Select p.id, p.product,p.status as statusId,p.description ,p.originalAuthorEmployeeId,p.notes  from ddlProducts p";

            string sql = "SELECT p.id, p.product,p.status as status,p.description as statusName,p.originalAuthorEmployeeId,p.notes, " +
 "(select top 1 name from ddlCompany where id = p.companyid) " +
  " from ddlProducts p where p.product like 'None' " +
 " UNION ALL " +
 " Select p.id, p.product,p.status as statusId,p.description ,p.originalAuthorEmployeeId,p.notes, " +
 " (select top 1 name from ddlCompany where id = p.companyid) " +
 " from ddlProducts p order by product asc ";

            return this.Factory.ExecuteObjects<ProductGridViewModel>(sql);
        }





        public DataTable GetAssignedEmpl(int emplId)
        {
           string sql ="select [product] from [CallCenterFinal].[dbo].[ddlProducts] inner join [CallCenterFinal].[dbo].[ccCommunications] on [CallCenterFinal].[dbo].[ddlProducts].[id] = [CallCenterFinal].[dbo].[ccCommunications].[productID] where [CallCenterFinal].[dbo].[ccCommunications].[assignedEmployeeID] = " + emplId;

            return this.Factory.ExecuteTable(sql);
        }

        public int GetProductIdByName(string product)
        {
            int result = 0;
            string sql = string.Format("select id from ddlProducts where product = '{0}'", product);
            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

        }


        public List<ProductEmployee> GetAuthorEmployee()

        {
            string sql = "SELECT 0 as originalAuthorEmployeeId, 'None' as empName UNION ALL SELECT distinct cc.id as originalAuthorEmployeeId, cc.lastName+', '+cc.firstname as empName from ccContacts cc INNER JOIN ddlEmployees e ON e.contactId = cc.id";

            return this.Factory.ExecuteObjects<ProductEmployee>(sql);

        }



        public List<ProductGridViewModel> GetAllGrid() //Gets all the data form the contacts table
        {
            string sql = "Select p.*, cc.lastName+', '+cc.firstname as empName,p.originalAuthorEmployeeId as AuthorEmployeeId, p.companyId as vendorcompanyId, p.status as statusId,  s.description as statusName, " +
                                                      " c.name as companyname from ddlProducts p" +
                                                      " LEFT JOIN ddlcompany c ON c.id = p.companyId " +
                                                      " LEFT JOIN ccContacts cc ON cc.id = p.originalAuthorEmployeeId " +
                                                      " LEFT JOIN ddlEmployees e ON e.contactId = cc.id " +
                                                      " LEFT JOIN ddlStatus s ON s.id = p.status " +
                                                      " WHERE p.id <> 5";

            return this.Factory.ExecuteObjects<ProductGridViewModel>(sql);
        }

        public int Insert(string product, int status, string description, int originalAuthorEmployeeId,
                         string notes, DateTime dateAdded, int companyId) //insert data to a contact table
        {
            int nRtn = -1;
            string sql = "INSERT INTO ddlProducts (product, status, description, originalAuthorEmployeeId,notes,dateAdded,companyId)" +
                                 " Values(@product, @status, @description,@originalAuthorEmployeeId,@notes,@dateAdded,@companyId) SELECT @pResult =@@IDENTITY";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@product", SqlDbType.NChar, 50, product);
                this.Factory.AddParameter(cmd, "@originalAuthorEmployeeId", SqlDbType.Int, 10, originalAuthorEmployeeId);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@status", SqlDbType.Int, 10, status);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.VarChar, 50, description);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.VarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@dateAdded", SqlDbType.DateTime, 10, dateAdded);


                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }


            return nRtn; //retruning the data stored in the table

        }
        public int Update(int id, string product, int status, string description, int originalAuthorEmployeeId,
                            string notes, int companyId, DateTime dateUpdated)
        {
            int nRtn = -1;
            string sql = "Update ddlProducts SET product=@product, status=@status, description=@description," +
                                            " originalAuthorEmployeeId = @originalAuthorEmployeeId, notes = @notes," +
                                            " companyId=@companyId, dateUpdated=@dateUpdated WHERE id =@id";




            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@product", SqlDbType.NVarChar, 50, product);
                this.Factory.AddParameter(cmd, "@originalAuthorEmployeeId", SqlDbType.Int, 10, originalAuthorEmployeeId);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@status", SqlDbType.Int, 10, status);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.VarChar, 50, description);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.VarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@dateUpdated", SqlDbType.DateTime, 10, dateUpdated);
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

                nRtn = id;
                return nRtn; //retruning the data stored in the table
            }
        }
        public void Delete(int nAddressType) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ddlProducts " +
                  "WHERE ID = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nAddressType);
                this.Factory.ExecuteNonQuery(cmd);
            }
        }    
           

        public void DeleteAll() //delete all the rows in the data.
        {
          string sql = "DELETE FROM ddlProducts ";

            this.Factory.ExecuteNonQuery(sql);
        }

    }

}

